package com.pai4j.ugc.repository.dao.custom;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository
public class MomentKeywordQueryRepository {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 多关键词严格AND/OR/NOT分页，返回momentId列表与总数
     * mode: AND / OR / NOT
     * 排序：time_desc/time_asc 或 score_desc/score_asc（基于主表）
     */
    public Result queryByKeywords(List<String> keywords, String mode, String sort, int pageNo, int pageSize) {
        if (keywords == null) keywords = Collections.emptyList();
        String sanitizedSort = (sort == null || sort.isBlank()) ? "time_desc" : sort.toLowerCase();
        String m = (mode == null || mode.isBlank()) ? "and" : mode.toLowerCase();

        StringBuilder sql = new StringBuilder();
        StringBuilder countSql = new StringBuilder();

        // 基于倒排表做聚合
        // OR: keyword IN (:keywords)
        // AND: keyword IN (:keywords) GROUP BY moment_id HAVING COUNT(DISTINCT keyword) = :kwSize
        // NOT: moment_id NOT IN (select moment_id from ... where keyword in (:keywords))

        if ("not".equals(m)) {
            sql.append("SELECT m.id FROM pai4j_moment m WHERE m.id NOT IN ( ")
               .append("SELECT k.moment_id FROM pai4j_moment_ai_keyword k WHERE k.keyword IN (:keywords)")
               .append(") ");
            countSql.append("SELECT COUNT(*) FROM pai4j_moment m WHERE m.id NOT IN ( ")
                    .append("SELECT k.moment_id FROM pai4j_moment_ai_keyword k WHERE k.keyword IN (:keywords)")
                    .append(") ");
        } else {
            sql.append("SELECT k.moment_id FROM pai4j_moment_ai_keyword k WHERE 1=1 ");
            countSql.append("SELECT COUNT(*) FROM (")
                    .append("SELECT k.moment_id FROM pai4j_moment_ai_keyword k WHERE 1=1 ");

            if (!keywords.isEmpty()) {
                sql.append(" AND k.keyword IN (:keywords) ");
                countSql.append(" AND k.keyword IN (:keywords) ");
            }
            sql.append(" GROUP BY k.moment_id ");
            countSql.append(" GROUP BY k.moment_id ");
            if ("and".equals(m)) {
                sql.append(" HAVING COUNT(DISTINCT k.keyword) = :kwSize ");
                countSql.append(" HAVING COUNT(DISTINCT k.keyword) = :kwSize ");
            }
            countSql.append(") t");
        }

        // 排序（基于主表字段）
        String orderClause = " ORDER BY m.create_date DESC";
        if ("time_asc".equals(sanitizedSort)) {
            orderClause = " ORDER BY m.create_date ASC";
        } else if ("score_desc".equals(sanitizedSort)) {
            orderClause = " ORDER BY m.review_score DESC";
        } else if ("score_asc".equals(sanitizedSort)) {
            orderClause = " ORDER BY m.review_score ASC";
        }

        // 将moment_id结果join回主表以支持排序
        String finalSql;
        if ("not".equals(m)) {
            finalSql = sql.append(orderClause).toString();
        } else {
            finalSql = "SELECT m.id FROM pai4j_moment m JOIN (" + sql + ") ids ON m.id = ids.moment_id " + orderClause;
        }

        Query dataQuery = entityManager.createNativeQuery(finalSql);
        Query cntQuery = entityManager.createNativeQuery(countSql.toString());

        if (!keywords.isEmpty()) {
            dataQuery.setParameter("keywords", keywords);
            cntQuery.setParameter("keywords", keywords);
        }
        if ("and".equals(m)) {
            dataQuery.setParameter("kwSize", keywords.size());
            cntQuery.setParameter("kwSize", keywords.size());
        }

        dataQuery.setFirstResult((pageNo - 1) * pageSize);
        dataQuery.setMaxResults(pageSize);

        @SuppressWarnings("unchecked")
        List<Number> ids = dataQuery.getResultList();
        Number total = ((Number) cntQuery.getSingleResult());

        List<Long> momentIds = ids.stream().map(Number::longValue).toList();
        return new Result(momentIds, total.longValue());
    }

    public record Result(List<Long> momentIds, long total) {}
}

